-- This script demonstrates the danger of using the default
-- cursor type.
SET NOCOUNT ON
USE Northgale
go
-- The same as before.
EXEC setup_discounts

-- These are variables to monitor the solution.
DECLARE @rows          int = 0,
        @starttime     datetime2(3) = sysdatetime(),
        @afterms       int

DECLARE @OrderID       int,
        @Freight       decimal(10,2),
        @CustomerID    nchar(5),
        @OldDiscount   decimal(5,2),
        @Country       nvarchar(15),
        @City          nvarchar(25),
        @NewDiscount   decimal(5,2),
        @ProductAmount decimal(10,2),
        @OrderAmount   decimal(10,2)

-- When using the cursor, we join in both orders and customers
-- at once. For the sake of the effect for the demo, we are not
-- using the temp table, but join with the function directly.
DECLARE order_cur CURSOR STATIC LOCAL FOR
   SELECT t.OrderID, O.CustomerID, O.Freight, O.Discount,
          C.City, C.Country
   FROM   dbo.OrdersToModify() t
   JOIN   dbo.Orders O ON O.OrderID = t.OrderID 
   JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID

OPEN order_cur

WHILE 1 = 1
BEGIN
   FETCH order_cur INTO @OrderID, @CustomerID, @Freight, @OldDiscount,
                        @City, @Country
   IF @@fetch_status <> 0
      BREAK

   -- Determine discount.
   IF EXISTS (SELECT * 
              FROM   dbo.Discounts 
              WHERE  CustomerID = @CustomerID)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID = @CustomerID
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  City = @City)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  City = @City
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  Country = @Country)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  Country = @Country
   END 
   ELSE IF EXISTS (SELECT *
                   FROM  Discounts
                   WHERE CustomerID IS NULL
                     AND City       IS NULL
                     AND Country    IS NULL) 
   BEGIN 
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID IS NULL
        AND  City       IS NULL
        AND  Country    IS NULL 
   END
   ELSE 
      SELECT @NewDiscount = 0

   IF @NewDiscount <> @OldDiscount
   BEGIN
      SELECT @ProductAmount = SUM(UnitPrice * Quantity)
      FROM   dbo.[Order Details]
      WHERE  OrderID = @OrderID

      SELECT @OrderAmount = @ProductAmount * (1 - @NewDiscount/100) + 
                            @Freight

      UPDATE dbo.Orders
      SET    Discount    = @NewDiscount,
             TotalAmount = @OrderAmount
      WHERE  OrderID = @OrderID
   END

   -- Here we monitor the progress.
   SELECT @rows += 1
   IF @rows % 1000 = 0
   BEGIN
      SELECT @afterms = datediff(ms, @starttime, sysdatetime())
      RAISERROR('%d rows processed in %d ms.', 0, 1, @rows, @afterms) WITH NOWAIT
   END
END

DEALLOCATE order_cur

SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('%d rows processed in %d ms.', 0, 1, @rows, @afterms) WITH NOWAIT
go


-- Here is the same script with a cursor variable instead.
SET NOCOUNT ON
USE Northgale
go
EXEC setup_discounts

-- These are variables to monitor the solution.
DECLARE @rows          int = 0,
        @starttime     datetime2(3) = sysdatetime(),
        @afterms       int

DECLARE @order_cur     CURSOR,
        @OrderID       int,
        @Freight       decimal(10,2),
        @CustomerID    nchar(5),
        @OldDiscount   decimal(5,2),
        @Country       nvarchar(15),
        @City          nvarchar(25),
        @NewDiscount   decimal(5,2),
        @ProductAmount decimal(10,2),
        @OrderAmount   decimal(10,2)

SET @order_cur = CURSOR STATIC FOR
   SELECT t.OrderID, O.CustomerID, O.Freight, 
          O.Discount, C.City, C.Country
   FROM   dbo.OrdersToModify() t
   JOIN   dbo.Orders O ON O.OrderID = t.OrderID 
   JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID

OPEN @order_cur

WHILE 1 = 1
BEGIN
   FETCH @order_cur INTO @OrderID, @CustomerID, @Freight, 
                         @OldDiscount, @City, @Country
   IF @@fetch_status <> 0
      BREAK

   -- Determine discount.
   IF EXISTS (SELECT * 
              FROM   dbo.Discounts 
              WHERE  CustomerID = @CustomerID)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID = @CustomerID
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  City = @City)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  City = @City
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  Country = @Country)
   BEGIN
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  Country = @Country
   END 
   ELSE IF EXISTS (SELECT *
                   FROM  Discounts
                   WHERE CustomerID IS NULL
                     AND City       IS NULL
                     AND Country    IS NULL) 
   BEGIN 
      SELECT @NewDiscount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID IS NULL
        AND  City       IS NULL
        AND  Country    IS NULL 
   END
   ELSE 
      SELECT @NewDiscount = 0

   IF @NewDiscount <> @OldDiscount
   BEGIN
      SELECT @ProductAmount = SUM(UnitPrice * Quantity)
      FROM   dbo.[Order Details]
      WHERE  OrderID = @OrderID

      SELECT @OrderAmount = @ProductAmount * (1 - @NewDiscount/100) + 
                            @Freight

      UPDATE dbo.Orders
      SET    Discount    = @NewDiscount,
             TotalAmount = @OrderAmount
      WHERE  OrderID = @OrderID
   END

   -- Here we monitor the progress.
   SELECT @rows += 1
   IF @rows % 1000 = 0
   BEGIN
      SELECT @afterms = datediff(ms, @starttime, sysdatetime())
      RAISERROR('%d rows processed in %d ms.', 0, 1, 
                @rows, @afterms) WITH NOWAIT
   END
END

SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('%d rows processed in %d ms.', 0, 1, 
           @rows, @afterms) WITH NOWAIT
go
-- Now for the final demo, remove STATIC from any of the cursors
-- above, and performance will go south.
